package com.example.chatplatform.mapper;

import com.example.chatplatform.model.entity.CronyMessage;
import com.example.chatplatform.model.entity.GroupMessage;
import com.example.chatplatform.model.vo.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface MessageMapper {

    @Select("SELECT user_id, user_name, user_avatar, message, message_time\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        user_id, \n" +
            "        user_name, \n" +
            "        user_avatar, \n" +
            "        message, \n" +
            "        message_time,\n" +
            "        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY message_time DESC) AS rn\n" +
            "    FROM (\n" +
            "        SELECT user_id, user_name, user_avatar, message, message_time \n" +
            "        FROM crony_message\n" +
            "        JOIN user ON user.user_id = crony_message.receive_id\n" +
            "        WHERE messaging_id = #{userId}\n" +
            "        \n" +
            "        UNION\n" +
            "        \n" +
            "        SELECT user_id, user_name, user_avatar, message, message_time \n" +
            "        FROM crony_message\n" +
            "        JOIN user ON user.user_id = crony_message.messaging_id\n" +
            "        WHERE receive_id = #{userId}\n" +
            "    ) AS combined_messages\n" +
            ") AS ranked_messages\n" +
            "WHERE rn = 1;")
    @Results({
            @Result(property = "cronyId", column = "user_id"),
            @Result(property = "cronyName", column = "user_name"),
            @Result(property = "cronyAvatar", column = "user_avatar"),
            @Result(property = "latestMessage", column = "message"),
            @Result(property = "messageTime", column = "message_time")
    })
    List<CronyMessageListVO> cronyMessageList(Integer userId);

    @Select("SELECT group_id, group_name, group_avatar, user_id, user_name, message, message_time\n" +
            "FROM (\n" +
            "         SELECT\n" +
            "             g.group_id,\n" +
            "             g.group_name,\n" +
            "             g.group_avatar,\n" +
            "             u.user_id,\n" +
            "             u.user_name,\n" +
            "             gm.message,\n" +
            "             gm.message_time,\n" +
            "             ROW_NUMBER() OVER(PARTITION BY g.group_id ORDER BY gm.message_time DESC) AS rn\n" +
            "         FROM group_user gu\n" +
            "                  JOIN group_message gm ON gm.group_id = gu.group_id\n" +
            "                  JOIN `group` g ON g.group_id = gu.group_id\n" +
            "                  JOIN user u ON u.user_id = gm.user_id\n" +
            "         WHERE gu.user_id = #{userId}\n" +
            "     ) AS ranked_messages\n" +
            "WHERE rn = 1;")
    @Results({
            @Result(property = "groupId", column = "group_id"),
            @Result(property = "groupName", column = "group_name"),
            @Result(property = "groupAvatar", column = "group_avatar"),
            @Result(property = "latestUserId", column = "user_id"),
            @Result(property = "latestUserName", column = "user_name"),
            @Result(property = "latestMessage", column = "message"),
            @Result(property = "messageTime", column = "message_time")
    })
    List<GroupMessageListVO> groupMessageList(Integer userId);

    @Select("SELECT user_id, user_name, user_avatar, message, message_time\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        user_id, \n" +
            "        user_name, \n" +
            "        user_avatar, \n" +
            "        message, \n" +
            "        message_time,\n" +
            "        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY message_time DESC) AS rn\n" +
            "    FROM (\n" +
            "        SELECT user_id, user_name, user_avatar, message, message_time \n" +
            "        FROM crony_message\n" +
            "        JOIN user ON user.user_id = crony_message.receive_id\n" +
            "        WHERE messaging_id = #{userId}\n" +
            "        AND user_name LIKE CONCAT('%', #{searchChatValue}, '%')\n" +
            "        \n" +
            "        UNION\n" +
            "        \n" +
            "        SELECT user_id, user_name, user_avatar, message, message_time \n" +
            "        FROM crony_message\n" +
            "        JOIN user ON user.user_id = crony_message.messaging_id\n" +
            "        WHERE receive_id = #{userId}\n" +
            "        AND user_name LIKE CONCAT('%', #{searchChatValue}, '%')\n" +
            "    ) AS combined_messages\n" +
            ") AS ranked_messages\n" +
            "WHERE rn = 1;")
    @Results({
            @Result(property = "cronyId", column = "user_id"),
            @Result(property = "cronyName", column = "user_name"),
            @Result(property = "cronyAvatar", column = "user_avatar"),
            @Result(property = "latestMessage", column = "message"),
            @Result(property = "messageTime", column = "message_time")
    })
    List<CronyMessageListVO> searchCronyChat(Integer userId, String searchChatValue);

    @Select("SELECT group_id, group_name, group_avatar, user_id, user_name, message, message_time\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        g.group_id,\n" +
            "        g.group_name,\n" +
            "        g.group_avatar,\n" +
            "        u.user_id,\n" +
            "        u.user_name,\n" +
            "        gm.message,\n" +
            "        gm.message_time,\n" +
            "        ROW_NUMBER() OVER(PARTITION BY g.group_id ORDER BY gm.message_time DESC) AS rn\n" +
            "    FROM group_user gu\n" +
            "    JOIN group_message gm ON gm.group_id = gu.group_id\n" +
            "    JOIN `group` g ON g.group_id = gu.group_id\n" +
            "    JOIN user u ON u.user_id = gm.user_id\n" +
            "    WHERE gu.user_id = #{userId}\n" +
            "    AND g.group_name LIKE CONCAT('%', #{searchChatValue}, '%')\n" +
            ") AS ranked_messages\n" +
            "WHERE rn = 1;")
    @Results({
            @Result(property = "groupId", column = "group_id"),
            @Result(property = "groupName", column = "group_name"),
            @Result(property = "groupAvatar", column = "group_avatar"),
            @Result(property = "latestUserId", column = "user_id"),
            @Result(property = "latestUserName", column = "user_name"),
            @Result(property = "latestMessage", column = "message"),
            @Result(property = "messageTime", column = "message_time")
    })
    List<GroupMessageListVO> searchGroupChat(Integer userId, String searchChatValue);

    @Select("select user_name, user_avatar from user where user_id = #{cronyId}")
    @Results({
            @Result(property = "cronyName", column = "user_name"),
            @Result(property = "cronyAvatar", column = "user_avatar")
    })
    CronyMessageVO cronyInfo(Integer cronyId);

    @Select("select crony_message_id, message, message_time from crony_message where messaging_id = #{userId} and receive_id = #{cronyId}")
    @Results({
            @Result(property = "cronyMessageId", column = "crony_message_id"),
            @Result(property = "messageTime", column = "message_time")
    })
    List<CronyMessage> cronyMessageList0(Integer userId, Integer cronyId);

    @Select("select crony_message_id, message, message_time from crony_message where messaging_id = #{cronyId} and receive_id = #{userId}")
    @Results({
            @Result(property = "cronyMessageId", column = "crony_message_id"),
            @Result(property = "messageTime", column = "message_time")
    })
    List<CronyMessage> cronyMessageList1(Integer userId, Integer cronyId);

    @Select("select group_id, group_name, group_number from `group` where group_id = #{groupId}")
    @Results({
            @Result(property = "groupId", column = "group_id"),
            @Result(property = "groupName", column = "group_name"),
            @Result(property = "groupNumber", column = "group_number")
    })
    GroupMessageVO groupInfo(Integer groupId);

    @Select("select group_message.group_message_id, user.user_id, user.user_name, user.user_avatar, group_message.message, group_message.message_time from group_message" +
            " join user on user.user_id = group_message.user_id " +
            "where group_message.group_id = #{groupId} and group_message.user_id = #{userId}")
    @Results({
            @Result(property = "groupMessageId", column = "group_message_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "userName", column = "user_name"),
            @Result(property = "userAvatar", column = "user_avatar"),
            @Result(property = "messageTime", column = "message_time")
    })
    List<GroupMessage> groupMessageList0(Integer userId, Integer groupId);

    @Select("select group_message.group_message_id, user.user_id, user.user_name, user.user_avatar, group_message.message, group_message.message_time from group_message" +
            " join user on user.user_id = group_message.user_id " +
            "where group_message.group_id = #{groupId} and group_message.user_id != #{userId}")
    @Results({
            @Result(property = "groupMessageId", column = "group_message_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "userName", column = "user_name"),
            @Result(property = "userAvatar", column = "user_avatar"),
            @Result(property = "messageTime", column = "message_time")
    })
    List<GroupMessage> groupMessageList1(Integer userId, Integer groupId);

}
